--drop table if exists dongfeng.navinfo_stage_d00a;
--创建外部表
create external table if not exists dongfeng.navinfo_stage_d00a (
                           latitude                               int      comment   '纬度',
                           longitude                              int      comment   '经度',
                           originalLat                            int      comment   '原始纬度',
                           originalLng                            int      comment   '原始经度',
                           height                                 int      comment   '海拔',
                           direction                              int      comment   '方向',
                           gpsDate                                bigint      comment  '时间',
                           version                                int      comment   '版本',
                           engineIntakeManifoldPressure           int      comment   '发动机进气歧管压力',
                           engineIntakeManifoldTemperature        int      comment   '发动机进气歧管温度',
                           engineCoolantTemperature               int      comment   '发动机冷却液温度',
                           fuelTemperature                        int      comment   '燃油温度',
                           ambientTemperature                     double     comment '环境温度',
                           instrumentSpeed                        double     comment '仪表车速',
                           rotation                               double     comment '发动机转速',
                           serviceBrakeCircuit1AirPressure        int      comment   '行车制动回路1气压',
                           serviceBrakeCircuit2AirPressure        int      comment   '行车制动回路2气压',
                            fanSpeed                              double     comment '风扇转速',
                           accumulatedMileage                     bigint      comment  '累计里程',
                           subtotalMileage                        bigint      comment  '小计里程',
                            acceleratorPedalPosition              double     comment '加速踏板位置',
                           currentRotationLoad                    int      comment   '当前转速下的负载',
                           serviceBrake                           int      comment   '行车制动',
                           escInstrumentLightSignal               int      comment   'ESC仪表灯信号',
                           absActivationStatus                    int      comment   'ABS激活状态',
                           escFullActivation                      int      comment   'ESC全激活',
                           escBrakeLightRequestSignal             int      comment   'ESC制动灯请求信号',
                           antiRolloverActivationEngine           int      comment   '防侧翻激活发动机',
                           antiRolloverActivationServiceBrake     int      comment   '防侧翻激活行车制动',
                           directionControlActivatesEngine        int      comment   '方向控制激活发动机',
                           directionControlActivatesServiceBrake  int      comment   '方向控制激活行车制动',
                           escActivateTrailer                     int      comment   'ESC激活挂车',
                           steeringWheelAngleSensorType           int      comment   '方向盘角度传感器类型',
                           exhaustBrakingState                    int      comment   '排气制动状态',
                           inCylinderBrake2Status                 int      comment   '缸内制动2状态',
                           inCylinderBrake1Status                 int      comment   '缸内制动1状态',
                           steeringWheelTurnsNumber               int      comment   '方向盘圈数',
                           auxiliaryBrakeGear                     int      comment   '辅助制动挡位',
                            steeringWheelAngle                    double     comment '方向盘角度',
                            yawRate                               double     comment '横摆率',
                            lateralAcceleration                   double     comment '侧向加速度',
                            longitudinalAcceleration              double     comment '纵向加速度',
                            brakePedalTravel                      double     comment '制动踏板行程',
                            frontWheelSpeed                       double     comment '前轮速',
                            rearAxleOneLeftWheelSpeed             double     comment '后一轴左轮速',
                            rearAxleOneRightWheelSpeed            double     comment '后一轴右轮速',
                            rearAxleTwoLeftWheelSpeed             double     comment '后二轴左轮速',
                            rearAxleTwoRightWheelSpeed            double     comment '后二轴右轮速',
                           erc1ActualTorquePercentageRetarder     int      comment   'ERC1实际扭矩百分比-缓速器',
                           erc1MaximumTorquePercentageRetarder    int      comment   'ERC1最大扭矩百分比-缓速器',
                           erc1ActualTorquePercentageEngine       int      comment   'ERC1实际扭矩百分比-发动机',
                           erc1MaximumTorquePercentageEngine      int      comment   'ERC1最大扭矩百分比-发动机',
                           terminalId                             int      comment   '终端号',
                           serialNumber                           int      comment   '流水号',
                           retransmissionPackets                  int      comment   '是否重传包',
                           downshiftReminder                      int      comment   '降档提醒',
                           constantSpeedGear                      int      comment   '有无恒速挡',
                           engineAirFilterPressureDifference     double     comment  '发动机空滤压差1（KPa/bit）',
                           standingCircuitPressureIndication      int      comment   '驻挂回路气压指示（Kpa）',
                           part_time                       INT    COMMENT '分区时间'
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/dongfeng/stage_import_d00a';

--创建目标表
create table if not exists dongfeng.navinfo_parquet_d00a(
 latitude                               int      comment   '纬度',
                           longitude                              int      comment   '经度',
                           originalLat                            int      comment   '原始纬度',
                           originalLng                            int      comment   '原始经度',
                           height                                 int      comment   '海拔',
                           direction                              int      comment   '方向',
                           gpsDate                                bigint      comment  '时间',
                           version                                int      comment   '版本',
                           engineIntakeManifoldPressure           int      comment   '发动机进气歧管压力',
                           engineIntakeManifoldTemperature        int      comment   '发动机进气歧管温度',
                           engineCoolantTemperature               int      comment   '发动机冷却液温度',
                           fuelTemperature                        int      comment   '燃油温度',
                           ambientTemperature                     double     comment '环境温度',
                           instrumentSpeed                        double     comment '仪表车速',
                           rotation                               double     comment '发动机转速',
                           serviceBrakeCircuit1AirPressure        int      comment   '行车制动回路1气压',
                           serviceBrakeCircuit2AirPressure        int      comment   '行车制动回路2气压',
                            fanSpeed                              double     comment '风扇转速',
                           accumulatedMileage                     bigint      comment  '累计里程',
                           subtotalMileage                        bigint      comment  '小计里程',
                            acceleratorPedalPosition              double     comment '加速踏板位置',
                           currentRotationLoad                    int      comment   '当前转速下的负载',
                           serviceBrake                           int      comment   '行车制动',
                           escInstrumentLightSignal               int      comment   'ESC仪表灯信号',
                           absActivationStatus                    int      comment   'ABS激活状态',
                           escFullActivation                      int      comment   'ESC全激活',
                           escBrakeLightRequestSignal             int      comment   'ESC制动灯请求信号',
                           antiRolloverActivationEngine           int      comment   '防侧翻激活发动机',
                           antiRolloverActivationServiceBrake     int      comment   '防侧翻激活行车制动',
                           directionControlActivatesEngine        int      comment   '方向控制激活发动机',
                           directionControlActivatesServiceBrake  int      comment   '方向控制激活行车制动',
                           escActivateTrailer                     int      comment   'ESC激活挂车',
                           steeringWheelAngleSensorType           int      comment   '方向盘角度传感器类型',
                           exhaustBrakingState                    int      comment   '排气制动状态',
                           inCylinderBrake2Status                 int      comment   '缸内制动2状态',
                           inCylinderBrake1Status                 int      comment   '缸内制动1状态',
                           steeringWheelTurnsNumber               int      comment   '方向盘圈数',
                           auxiliaryBrakeGear                     int      comment   '辅助制动挡位',
                            steeringWheelAngle                    double     comment '方向盘角度',
                            yawRate                               double     comment '横摆率',
                            lateralAcceleration                   double     comment '侧向加速度',
                            longitudinalAcceleration              double     comment '纵向加速度',
                            brakePedalTravel                      double     comment '制动踏板行程',
                            frontWheelSpeed                       double     comment '前轮速',
                            rearAxleOneLeftWheelSpeed             double     comment '后一轴左轮速',
                            rearAxleOneRightWheelSpeed            double     comment '后一轴右轮速',
                            rearAxleTwoLeftWheelSpeed             double     comment '后二轴左轮速',
                            rearAxleTwoRightWheelSpeed            double     comment '后二轴右轮速',
                           erc1ActualTorquePercentageRetarder     int      comment   'ERC1实际扭矩百分比-缓速器',
                           erc1MaximumTorquePercentageRetarder    int      comment   'ERC1最大扭矩百分比-缓速器',
                           erc1ActualTorquePercentageEngine       int      comment   'ERC1实际扭矩百分比-发动机',
                           erc1MaximumTorquePercentageEngine      int      comment   'ERC1最大扭矩百分比-发动机',
                           terminalId                             int      comment   '终端号',
                           serialNumber                           int      comment   '流水号',
                           retransmissionPackets                  int      comment   '是否重传包',
                           downshiftReminder                      int      comment   '降档提醒',
                           constantSpeedGear                      int      comment   '有无恒速挡',
                           engineAirFilterPressureDifference     double     comment  '发动机空滤压差1（KPa/bit）',
                           standingCircuitPressureIndication      int      comment   '驻挂回路气压指示（Kpa）',
                           create_time                     STRING              COMMENT '数据创建时间'
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
